﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;

namespace DAO
{
    public static class Khoi_MonHocDAO
    {
        public static Khoi_MonHocDTO LayDuLieuTuDataRow(DataRow dr)
        {
            Khoi_MonHocDTO item = null;
            try
            {
                item = new Khoi_MonHocDTO();
                item.IDKhoi = dr["IDKhoi"].ToString();
                item.IDMonHoc = dr["IDMonHoc"].ToString();
                item.SoTietPhaiHoc = int.Parse(dr["SoTietPhaiHoc"].ToString());
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy thông tin: " + ex.ToString());
            }
            return item;
        }

        //lấy danh sách đầy đủ
        public static DataTable GetAllMonHocTheoKhoi(string maKhoi)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = "select mh.TenMonHoc,kmh.SoTietTrongTuan, kmh.SoTietTrenBuoi from MonHoc mh,Khoi_MonHoc kmh where kmh.IDMonHoc=mh.ID and kmh.IDKhoi=?";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDKhoi", maKhoi));
                dt = DataProvider.ExecuteNonQuery(sql, arrParameter);
            }
            catch (Exception)
            {
                throw new Exception("Không lấy được danh sách môn học theo khối");
            }
            return dt;
        }

        //hàm lấy ra những môn không học của khối đó

        public static DataTable GetAllMonHoc(string maKhoi)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = "select mh.TenMonHoc from MonHoc mh where  not exists(select * from Khoi_MonHoc kmh where kmh.IDMonHoc=mh.ID and kmh.IDKhoi=?)";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDKhoi", maKhoi));
                dt = DataProvider.ExecuteNonQuery(sql, arrParameter);
            }
            catch (Exception)
            {
                throw new Exception("Không lấy được danh sách môn học theo khối");
            }
            return dt;
        }

        //hàm lấy ra mã môn học của tên môn học
        public static DataTable GetMaMonHoc(string tenMonHoc)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = "select mh.ID from MonHoc mh where mh.TenMonHoc=?";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@ID", tenMonHoc));
                dt = DataProvider.ExecuteNonQuery(sql, arrParameter);
            }
            catch (Exception)
            {
                throw new Exception("Không lấy được danh sách môn học theo khối");
            }
            return dt;
        }

        //phần của Luân
        //lấy danh sách đầy đủ
        public static List<Khoi_MonHocDTO> LayDanhSach()
        {
            List<Khoi_MonHocDTO> lstKhoi_MonHocDTO = null;
            try
            {
                lstKhoi_MonHocDTO = new List<Khoi_MonHocDTO>();
                String query = "SELECT * From Khoi_MonHoc";
                DataTable dt = DataProvider.ExecuteQuery(query);
                foreach (DataRow dr in dt.Rows)
                {
                    Khoi_MonHocDTO item = new Khoi_MonHocDTO();
                    item = LayDuLieuTuDataRow(dr);
                    lstKhoi_MonHocDTO.Add(item);
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return lstKhoi_MonHocDTO;
        }


        //thêm
        public static Boolean Them(ref Khoi_MonHocDTO item)
        {
            Boolean kq = false;
            try
            {
                String strQuery = "Insert into Khoi_MonHoc values (?,?,?,?)";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDKhoi", item.IDKhoi));
                arrParameter.Add(new OleDbParameter("@IDMonHoc", item.IDMonHoc));
                arrParameter.Add(new OleDbParameter("@SoTietPhaiHoc", item.SoTietPhaiHoc));
                arrParameter.Add(new OleDbParameter("@SoTietTrenBuoi", item.SoTietTrenBuoi));
                DataTable dt = DataProvider.ExecuteNonQuery(strQuery, arrParameter);
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return kq;
        }

        //cập nhật
        public static Boolean CapNhat(ref Khoi_MonHocDTO item)
        {
            Boolean kq = false;
            try
            {
                String strQuery = "Update Khoi_MonHoc Set SoTietPhaiHoc = ? Where IDKhoi = ? And IDMonHoc = ?";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@SoTietPhaiHoc", item.SoTietPhaiHoc));
                arrParameter.Add(new OleDbParameter("@IDKhoi", item.IDKhoi));
                arrParameter.Add(new OleDbParameter("@IDMonHoc", item.IDMonHoc));
                DataTable dt = DataProvider.ExecuteNonQuery(strQuery, arrParameter);
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return kq;
        }

        //xóa
        public static Boolean Xoa(String idMonHoc, String idKhoi)
        {
            Boolean kq = false;
            try
            {
                String strQuery = "Delete From Khoi_MonHoc Where IDMonHoc = ? and IDKhoi = ?";
                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDMonHoc", idMonHoc));
                arrParameter.Add(new OleDbParameter("@IDKhoi", idKhoi));
                DataTable dt = DataProvider.ExecuteNonQuery(strQuery, arrParameter);
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return kq;
        }

        //get so tiet max
        public static string GetTongSoTietHoc(string item)
        {
            try
            {
                String strQuery = "select * from Khoi Where ID = '" + item + "'";
                DataTable table = DataProvider.ExecuteQuery(strQuery);
                if (table.Rows.Count > 0)
                    return table.Rows[0][2].ToString();
                return "";
            }
            catch (Exception ex)
            {
                return "";
                throw new Exception(ex.ToString());
            }
        }

        public static string GetSoTietTrongTuan(Khoi_MonHocDTO item)
        {
            try
            {
                String strQuery = "select * from Khoi_MonHoc Where IDKhoi = '" + item.IDKhoi + "' And IDMonHoc = '" + item.IDMonHoc + "'";
                DataTable table = DataProvider.ExecuteQuery(strQuery);
                if (table.Rows.Count > 0)
                    return table.Rows[0][2].ToString();
                return "";
            }
            catch (Exception ex)
            {
                return "";
                throw new Exception(ex.ToString());
            }
        }

        public static DataTable GetAllKhoi()
        {
            DataTable result = new DataTable();
            try
            {
                string sql = "SELECT * From Khoi";
                result = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        static List<string> SelectMonHoc()
        {
            List<string> result = new List<string>();
            try
            {
                string sql = "select IDMonHoc from Khoi_MonHoc group by IDMonHoc";
                DataTable monhoc = DataProvider.ExecuteQuery(sql);
                if (monhoc.Rows.Count > 0)
                {
                    for (int i = 0; i < monhoc.Rows.Count; i++)
                    {
                        result.Add(monhoc.Rows[i][0].ToString());
                    }
                }

            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        //Get Ten mon hoc follow ID
        static string GetTenMonHoc(string id)
        {
            string tenmonhoc = "";
            DataTable result = new DataTable();
            try
            {
                string sql = "SELECT TenMonHoc From MonHoc where id='" + id + "'";

                result = DataProvider.ExecuteQuery(sql);
                if (result.Rows.Count > 0)
                {
                    tenmonhoc = result.Rows[0][0].ToString();
                }
            }
            catch (Exception)
            {

                throw;
            }
            return tenmonhoc;
        }

        public static Boolean CheckExit(Khoi_MonHocDTO item)
        {
            try
            {
                String strQuery = "select * from Khoi_MonHoc Where IDKhoi = '" + item.IDKhoi + "' And IDMonHoc = '" + item.IDMonHoc + "'";
                DataTable table = DataProvider.ExecuteQuery(strQuery);
                if (table.Rows.Count > 0)
                    return true;
                return false;
            }
            catch (Exception ex)
            {
                return false;
                throw new Exception(ex.ToString());
            }
        }

        //Select All Khoi of MonHoc
        static DataTable SelectKhoiOfMonHoc(string id)
        {
            DataTable result = new DataTable();
            try
            {
                string sql = "SELECT * From Khoi_MonHoc where idmonhoc='" + id + "'";
                result = DataProvider.ExecuteQuery(sql);
            }
            catch (Exception)
            {

                throw;
            }
            return result;

        }

        //Make DataTable for datagridview
        public static DataTable MakeDataTable()
        {
            DataTable result = new DataTable();
            DataColumn stt = new DataColumn("STT", typeof(string));
            result.Columns.Add(stt);
            DataColumn tenmonhoc = new DataColumn("TenMonHoc", typeof(string));
            result.Columns.Add(tenmonhoc);
            DataColumn khoi1 = new DataColumn("Khoi1", typeof(string));
            result.Columns.Add(khoi1);
            DataColumn khoi2 = new DataColumn("Khoi2", typeof(string));
            result.Columns.Add(khoi2);
            DataColumn khoi3 = new DataColumn("Khoi3", typeof(string));
            result.Columns.Add(khoi3);
            DataColumn khoi4 = new DataColumn("Khoi4", typeof(string));
            result.Columns.Add(khoi4);
            DataColumn khoi5 = new DataColumn("Khoi5", typeof(string));
            result.Columns.Add(khoi5);

            DataColumn idmonhoc = new DataColumn("idmonhoc", typeof(string));
            result.Columns.Add(idmonhoc);

            List<string> listMonHoc = SelectMonHoc();
            for (int i = 0; i < listMonHoc.Count; i++)
            {
                DataRow row = result.NewRow();
                row[0] = (i + 1).ToString();
                row[1] = GetTenMonHoc(listMonHoc[i]);
                for (int x = 0; x < 5; x++)
                {
                    row[x + 2] = "0";
                }
                DataTable selectkhoi = SelectKhoiOfMonHoc(listMonHoc[i]);
                for (int j = 0; j < selectkhoi.Rows.Count; j++)
                {
                    string khoi = selectkhoi.Rows[j][0].ToString();
                    string sotiet = selectkhoi.Rows[j][2].ToString();
                    if (khoi.Equals("K001"))
                    {
                        row[2] = sotiet;
                    }
                    else if (khoi.Equals("K002"))
                    {
                        row[3] = sotiet;
                    }
                    else if (khoi.Equals("K003"))
                    {
                        row[4] = sotiet;
                    }
                    else if (khoi.Equals("K004"))
                    {
                        row[5] = sotiet;
                    }
                    else if (khoi.Equals("K005"))
                    {
                        row[6] = sotiet;
                    }
                }
                row[7] = listMonHoc[i];
                result.Rows.Add(row);
            }
            DataRow rowsum = result.NewRow();
            rowsum[1] = "Tổng Số Tiết";
            List<string> listKhoi = GetListKhoi();
            for (int k = 0; k < listKhoi.Count; k++)
            {
                string tongso = GetTongSoTietHoc(listKhoi[k]);
                rowsum[k + 2] = SumSoTietHocTrongTuan(listKhoi[k]) + "/" + tongso;
            }
            result.Rows.Add(rowsum);
            return result;

        }

        //Get All Khoi in Khoi_MonHoc
        public static List<string> GetListKhoi()
        {
            List<string> result = new List<string>();
            try
            {
                string sql = "select idkhoi from khoi_monhoc group by idkhoi";
                DataTable monhoc = DataProvider.ExecuteQuery(sql);
                if (monhoc.Rows.Count > 0)
                {
                    for (int i = 0; i < monhoc.Rows.Count; i++)
                    {
                        result.Add(monhoc.Rows[i][0].ToString());
                    }
                }

            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        public static string SumSoTietHocTrongTuan(string idkhoi)
        {
            string result = "0";
            DataTable table = new DataTable();
            try
            {
                string sql = "SELECT sum(SoTietTrongTuan) From Khoi_MonHoc where idKhoi='" + idkhoi + "'";

                table = DataProvider.ExecuteQuery(sql);
                if (table.Rows.Count > 0)
                {
                    result = table.Rows[0][0].ToString();
                }
            }
            catch (Exception)
            {
                return result;
            }
            if (result == "")
                result = "0";
            return result;
        }


    }
}
